# -*- coding: utf-8 -*-

###########################################################################
#
#   Copyright (c) 2020  Bresee, Inc. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License"
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
###########################################################################

# mysql operation 

import pymysql

from pymysql.cursors import DictCursor
from dbutils.pooled_db import PooledDB


MYSQL_HOST=""
MYSQL_PORT=3306
MYSQL_USER="crawler"
MYSQL_DATABASE="crawler_deliver"
MYSQL_PASSWORD=""


class BasePymysqlPool(object):
    def __init__(self):
        self.db_host = MYSQL_HOST
        self.db_port = int(MYSQL_PORT)
        self.user = MYSQL_USER
        self.password = str(MYSQL_PASSWORD)
        self.db = MYSQL_DATABASE
        self.conn = None
        self.cursor = None

class PymysqlPool(BasePymysqlPool):
    """
    MYSQL ： conn = Mysql.getConn()
             conn.close()或del conn
    """
 
    __pool = None

    def __init__(self):
        super(PymysqlPool, self).__init__()
      
        self._conn = self.__getConn()
        self._cursor = self._conn.cursor()

    def __getConn(self):
        """
        @summary: 
        @return MySQLdb.connection
        """
        if PymysqlPool.__pool is None:
            __pool = PooledDB(creator=pymysql,
                              mincached=1,
                              maxcached=20,
                              host=self.db_host,
                              port=self.db_port,
                              user=self.user,
                              passwd=self.password,
                              db=self.db,
                              use_unicode=True,
                              charset="utf8",
                              cursorclass=DictCursor,
                              )
        return __pool.connection()

    def getAll(self, sql, param=None):
        """
        @summary: 
        @param sql:
        @param param: 
        @return: result list(dict )/boolean 
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchall()
        else:
            result = False
        return result

    def getOne(self, sql, param=None):
        """
        @summary: 
        @param sql:
        @param param: 
        @return: result list/boolean 
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchone()
        else:
            result = False
        return result

    def getMany(self, sql, num, param=None):
        """
        @summary: 
        @param sql:
        @param num:
        @param param: 
        @return: result list/boolean
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchmany(num)
        else:
            result = False
        return result

    def insertMany(self, sql, values):
        """
        @summary: 
        @param sql:
        @param values: tuple(tuple)/list[list]
        @return: count 
        """
        count = self._cursor.executemany(sql, values)
        return count

    def __query(self, sql, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        return count

    def update(self, sql, param=None):
        """
        @summary:  
        @param sql: ＳＱＬ (%s,%s)
        @param param:  tuple/list
        @return: count  
        """
        return self.__query(sql, param)

    def insert(self, sql, param=None):
        """
        @summary:  
        @param sql: ＳＱＬ (%s,%s)
        @param param:   tuple/list
        @return: count  
        """
        return self.__query(sql, param)

    '''Execute the statement of table creation '''
    def create_table(self,sql):
        return self._cursor.execute(sql)

    def insert_data(self, table_name: str, data) -> bool:
        '''
        
        @:param table_name  
        @:param data  
        '''
        try:
            count = 0
            if isinstance(data, list):
                for item in data:
                    keys = ",".join(list(item.keys()))
                    values = ",".join([f"'{x}'" for x in list(item.values())])
                    sql = f"INSERT IGNORE INTO {table_name} ({keys}) VALUES ({values});"
                    count+=self._cursor.execute(sql)
            elif isinstance(data, dict):
                keys = ",".join(list(data.keys()))
                values = ",".join([f"'{x}'" for x in list(data.values())])
                sql = f"INSERT IGNORE INTO {table_name} ({keys}) VALUES ({values});"
                print(sql)
                count+=self._cursor.execute(sql)
            return count
        except Exception as ex:
            raise Exception(ex)

    def delete(self, sql, param=None):
        """
        @summary:  
        @param sql: ＳＱＬ (%s,%s)
        @param param:  tuple/list
        @return: count  
        """
        return self.__query(sql, param)

    def begin(self):
        """
        @summary:
        """
        self._conn.autocommit(0)

    def end(self, option='commit'):
        """
        @summary:
        """
        if option == 'commit':
            self._conn.commit()
        else:
            self._conn.rollback()

    def dispose(self, isEnd=1):
        """
        @summary:
        """
        if isEnd == 1:
            self.end('commit')
        else:
            self.end('rollback')
        self._cursor.close()
        self._conn.close()
